package com.delta.rafg.core.database.impl.util;

import java.awt.FlowLayout;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.swing.ButtonGroup;
import javax.swing.JButton;
import javax.swing.JCheckBox;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JProgressBar;
import javax.swing.JRadioButton;
import javax.swing.JTextField;

import com.delta.rafg.core.config.ConfigSettings;
import com.delta.rafg.core.database.DataHandlerFactory;
import com.delta.rafg.core.database.DataHandlerOptions;
import com.delta.rafg.core.database.IDataHandler;
import com.delta.rafg.core.database.SynchroHandler;
import com.delta.rafg.core.database.impl.ADataHandler;
import com.delta.rafg.core.exception.RAFGException;
import com.delta.rafg.core.log.RAFGLogger;
import com.delta.rafg.coredata.AObject;

/**
 * @author RAFG Development Team (D9127520)
 *
 */
public class GenerateSeedData extends AObject {

    /**
     *
     */
    private static final long serialVersionUID = 1391590255828764419L;

    /**
     *
     */
//    private static final String HEXES = "0123456789ABCDEF";

    /**
     *
     */
    private static final String DEFAULT_SOURCE_URL =
        "jdbc:oracle:thin:@si-d119test-db.delta.com:1521:D119TEST";

    /**
     *
     */
    private static final String DEFAULT_USERNAME = "cmsupdate";

    /**
     *
     */
    private static final String DEFAULT_PASSWORD = "etadpusmc1";

    /**
     *
     */
    private static final String DEFAULT_DEST_PATH = "rafg_seed_data";

    /**
    *
    */
    private static final String DEFAULT_DEST_USERNAME = "system";

    /**
    *
    */
    private static final String DEFAULT_DEST_PASSWORD = "delta1";

    /**
    *
    */
    private static final String DEFAULT_DEST_SCHEMA_SUFFIX = "_CACHE";

    /**
     *
     */
    private static final String DEFAULT_SEED_FILE = "rafg_ref_seed.sql";

    /**
    *
    */
    private static final String DEFAULT_DEST_SERVICE = "XE";

    /**
     *
     */
    private JTextField sourceUrlTextBox = null;
    /**
     *
     */
    private JTextField destPathTextBox = null;
    /**
     *
     */
    private JTextField sourceDbUser = null;
    /**
     *
     */
    private JTextField sourceDbPass = null;
    /**
     *
     */
    private JTextField destDbUser = null;
    /**
    *
    */
    private JTextField destDbPass = null;
    /**
    *
    */
    private JTextField destServiceJTextField = null;
    /**
    *
    */
    private JTextField destSchemaSuffixJTextField = null;
    /**
     *
     */
    private JCheckBox testMode = null;

    private JRadioButton rbOracle = null;

    private JRadioButton rbDerby = null;

    /**
    *
    */
    private JProgressBar progressBar = null;

    private String lastPathSelected = DEFAULT_DEST_PATH;

    private int currTableNbr = 1;
    private int nbrTables = 0;

    List<String> createdSchemas = new ArrayList<String>();

    /**
     * Turn on/off test data generation mode.  When data is extracted from
     * the reference data tables, it will be altered some with random data and
     * missing rows.  This allows for testing the syncronizing logic.
     */
    private boolean bTestMode = false;

    public enum SEED_DATA_OPT {
        DEST_PASS, SOURCE_URL, SOURCE_PROPID, SOURCE_USER, DEST_PATH,
        SOURCE_PASS, SEED_FILE, DEST_USER, DEST_SCHEMA_SUFFIX,
        DEST_SERVICE, DEST_DB_TYPE, GENERICDATASOURCECLASSES, 
        EXCLUDE_TABLES, ONLY_TABLES, APPEND_TO_DEST;
    }

    public enum DEST_DB_TYPE {
    	ORACLE, DERBY;
    }

    /**
     *
     */
    private java.util.Random random = new java.util.Random();

    private Thread workerThread = null;
    private boolean stopWorkerThread = false;

    /**
     *
     */
    private void loadGui() {
        JFrame window = new JFrame();
        window.setLayout(new GridLayout(0, 1));
        window.setSize(580,240);
        window.setTitle("Generate Seed Data for RAFG");

        JPanel panel = new JPanel(new FlowLayout());
        sourceUrlTextBox = new JTextField(DEFAULT_SOURCE_URL);
        sourceUrlTextBox.setColumns(30);
        panel.add(new JLabel("Source DB Url"));
        panel.add(sourceUrlTextBox);
        window.add(panel);

        panel = new JPanel(new FlowLayout());
        sourceDbUser = new JTextField(DEFAULT_USERNAME);
        sourceDbUser.setColumns(15);
        sourceDbPass = new JTextField(DEFAULT_PASSWORD);
        sourceDbPass.setColumns(15);
        panel.add(new JLabel("Username"));
        panel.add(sourceDbUser);
        panel.add(new JLabel("Password"));
        panel.add(sourceDbPass);
        window.add(panel);

        panel = new JPanel(new FlowLayout());
        destPathTextBox = new JTextField(DEFAULT_DEST_PATH);
        destPathTextBox.setColumns(30);
        JButton destPathSelector = new JButton("...");
        destPathSelector.setSize(20, 20);
        destPathSelector.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(final ActionEvent e) {
                JFileChooser chooser = new JFileChooser(new java.io.File(lastPathSelected));
                chooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
                int returnVal = chooser.showOpenDialog(((JButton) e.getSource()).getParent());
                if (returnVal == JFileChooser.APPROVE_OPTION) {
                    destPathTextBox.setText(chooser.getSelectedFile().getAbsolutePath());
                    destPathTextBox.setToolTipText(chooser.getSelectedFile().getPath());
                    lastPathSelected = chooser.getSelectedFile().getPath();
                }

            }
        }
        );
        panel.add(new JLabel("Destination Path"));
        panel.add(destPathTextBox);
        panel.add(destPathSelector);
        window.add(panel);

        panel = new JPanel(new FlowLayout());
        destDbUser = new JTextField(DEFAULT_DEST_USERNAME);
        destDbUser.setColumns(15);
        destDbPass = new JTextField(DEFAULT_DEST_PASSWORD);
        destDbPass.setColumns(15);
        panel.add(new JLabel("Dest Username"));
        panel.add(destDbUser);
        panel.add(new JLabel("Dest Password"));
        panel.add(destDbPass);
        window.add(panel);

        panel = new JPanel(new FlowLayout());

        destServiceJTextField = new JTextField(DEFAULT_DEST_SERVICE);
        destServiceJTextField.setColumns(15);
        panel.add(new JLabel("Dest Service"));
        panel.add(destServiceJTextField);
        destSchemaSuffixJTextField = new JTextField(DEFAULT_DEST_SCHEMA_SUFFIX);
        destSchemaSuffixJTextField.setColumns(15);
        panel.add(new JLabel("Dest Schema Suffix"));
        panel.add(destSchemaSuffixJTextField);
        window.add(panel);

        panel = new JPanel(new FlowLayout());

        rbDerby = new JRadioButton("Derby");
        rbOracle = new JRadioButton("Oracle");
        ButtonGroup bgDestDbType = new ButtonGroup();
        bgDestDbType.add(rbDerby);
        bgDestDbType.add(rbOracle);

        panel.add(rbDerby);
        panel.add(rbOracle);

        rbDerby.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
            	destServiceJTextField.setEnabled(false);
            	destDbUser.setEnabled(false);
            	destDbPass.setEnabled(false);
            }
        });

        rbOracle.addActionListener(new ActionListener() {
			@Override
			public void actionPerformed(ActionEvent e) {
            	destServiceJTextField.setEnabled(true);
            	destDbUser.setEnabled(true);
            	destDbPass.setEnabled(true);
			}
        });

        rbDerby.doClick();

        testMode = new JCheckBox("Test Mode");
        testMode.setToolTipText("Turn on/off test data generation mode.  \n"
                + "When data is extracted from the reference data tables, \n"
                + "it will be altered some with random data and missing rows.  \n"
                + "This allows for testing the syncronizing logic.");
        panel.add(testMode);

        progressBar = new JProgressBar(JProgressBar.HORIZONTAL);
        progressBar.setMaximum(100);
        progressBar.setStringPainted(true);
        JButton submitButton = new JButton("Submit");
        submitButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(final ActionEvent e) {
                JButton btn = (JButton) e.getSource();
                if (btn.getText().equals("Submit")) {
                    btn.setText("Stop");
                    bTestMode = testMode.isSelected();
                    Map<SEED_DATA_OPT, Object> seedOpts = new HashMap<SEED_DATA_OPT, Object>();
                    seedOpts.put(SEED_DATA_OPT.SOURCE_URL, sourceUrlTextBox.getText());
                    seedOpts.put(SEED_DATA_OPT.SOURCE_USER, sourceDbUser.getText());
                    seedOpts.put(SEED_DATA_OPT.SOURCE_PASS, sourceDbPass.getText());
                    seedOpts.put(SEED_DATA_OPT.DEST_PATH, destPathTextBox.getText());
                    seedOpts.put(SEED_DATA_OPT.DEST_USER, destDbUser.getText());
                    seedOpts.put(SEED_DATA_OPT.DEST_PASS, destDbPass.getText());
                    seedOpts.put(SEED_DATA_OPT.DEST_SCHEMA_SUFFIX, destSchemaSuffixJTextField.getText());
                    seedOpts.put(SEED_DATA_OPT.SEED_FILE, DEFAULT_SEED_FILE);
                    seedOpts.put(SEED_DATA_OPT.DEST_SERVICE, destServiceJTextField.getText());
                    seedOpts.put(SEED_DATA_OPT.DEST_DB_TYPE, getDestDbType());

                    WorkerRunnable workerRunnable = new WorkerRunnable();
                    workerRunnable.setSeedOpts(seedOpts);
                    workerThread = new Thread(workerRunnable);
                    stopWorkerThread = false;
                    workerThread.start();
    //                extractSeedData(seedOpts);
                } else {
                    stopWorkerThread = true;
                    btn.setText("Submit");
                }

            }
        }
        );
        JButton closeButton = new JButton("Close");
        closeButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(final ActionEvent e) {
                System.exit(0);
            }
        }
        );
        panel.add(submitButton);
        panel.add(closeButton);
        panel.add(progressBar);
        window.add(panel);

        window.setVisible(true);
        while (window.isVisible()) {
            try {
                Thread.sleep(1000);
            } catch (InterruptedException e) {
                //do nothing
            }
        }
        System.exit(0);
    }

    private String getDestDbType() {
    	if (rbOracle.isSelected()) {
    		return DEST_DB_TYPE.ORACLE.toString();
    	} else {
    		return DEST_DB_TYPE.DERBY.toString();
    	}
    }

    /**
     * @param sourceUrl - source Url to use to connect to CMS database
     * @param username - username to use for source database connection
     * @param password - password to use for source database connection
     * @param destPath - destination Path to write seed file
     * @param seedFile - filename of seed file (excluding path)
     */
    public void extractSeedData(Map<SEED_DATA_OPT,Object> extractOpts) {
        try {
        	Object oGenericDataSourceClasses =  extractOpts.get(SEED_DATA_OPT.GENERICDATASOURCECLASSES);

            ConfigSettings configSettings = ConfigSettings.newInstance();
            
            List<IDataHandler> sourceDataHandlers = null;
            
        	if (oGenericDataSourceClasses != null && oGenericDataSourceClasses instanceof List) {
        		List<?> genericDataSourceClasses = (List<?>) oGenericDataSourceClasses;

                sourceDataHandlers = new ArrayList<IDataHandler>(genericDataSourceClasses.size()+1);
        		for (Object c : genericDataSourceClasses) {
        			if (c instanceof String) {
	        			IDataHandler sourceDataHandler = DataHandlerFactory.getNewGenericDataHandler((String) c);
	                    DataHandlerOptions configOpts = new DataHandlerOptions();
	        			configOpts.setDataHandlerPropertyIdentifier((String) c);
	        			sourceDataHandler.initialize(configOpts);
	        			sourceDataHandlers.add(sourceDataHandler);
        			}
        		}
        		
        	} else {
                String sourceUrl = (String) extractOpts.get(SEED_DATA_OPT.SOURCE_URL);
                String username = (String) extractOpts.get(SEED_DATA_OPT.SOURCE_USER);
                String password = (String) extractOpts.get(SEED_DATA_OPT.SOURCE_PASS);
                String propId = (String) extractOpts.get(SEED_DATA_OPT.SOURCE_PROPID);
                
                if (sourceUrl != null) {
                	IDataHandler sourceDataHandler = DataHandlerFactory.getReferenceDataHandler();
                    DataHandlerOptions srcOpts = new DataHandlerOptions();
                    srcOpts.setConnectionUrl(sourceUrl);
                    srcOpts.setUsername(username);
                    srcOpts.setPassword(password);
                    //set to ignore any jndi settings in the config.properties file
                    srcOpts.addOverideOption(DataHandlerOptions.OVERIDE_OPTION.IGNORE_JNDI);

                    srcOpts.setConfigSettings(configSettings);
                    srcOpts.setDataHandlerPropertyIdentifier(propId);
                    sourceDataHandler.initialize(srcOpts);
                    sourceDataHandlers = new ArrayList<IDataHandler>(2);
                    sourceDataHandlers.add(sourceDataHandler);
                } else {
                	//if a source Url is not set, use settings from ConfigSettings 
                	SynchroHandler syncHandler = new SynchroHandler(); 
                	configSettings.setProperty(ADataHandler.PROP_DATASOURCE_CONFIG_PREFIX + ".ignoreJndi", "true");
                	syncHandler.initializeMasterDataHandlers(configSettings);
                	sourceDataHandlers = syncHandler.getMasterDataHandlers();
                }
        	}
        	
            boolean bAppend = (Boolean) extractOpts.get(SEED_DATA_OPT.APPEND_TO_DEST);
            for (IDataHandler sourceDataHandler : sourceDataHandlers) {
            	extractSeedData(sourceDataHandler, extractOpts, bAppend);
            	if(!bAppend) bAppend = true;
            }

        } catch (RAFGException e) {
            e.printStackTrace();
        }
    }

    public void extractSeedData(IDataHandler sourceDataHandler, Map<SEED_DATA_OPT, Object> seedOpts) {
    	extractSeedData(sourceDataHandler, seedOpts, false);
    }

    public void extractSeedData(IDataHandler sourceDataHandler, Map<SEED_DATA_OPT, Object> seedOpts, boolean bAppend) {
    	String destDbType = (String) seedOpts.get(SEED_DATA_OPT.DEST_DB_TYPE);
    	if (DEST_DB_TYPE.valueOf(destDbType) == DEST_DB_TYPE.DERBY) {
    		extractDerbySeedData(sourceDataHandler, seedOpts, bAppend);
    	} else {
    		extractOracleSeedData(sourceDataHandler, seedOpts, bAppend);
    	}
    }

    public void extractDerbySeedData(IDataHandler sourceDataHandler, Map<SEED_DATA_OPT, Object> seedOpts) {
    	extractDerbySeedData(sourceDataHandler, seedOpts, false);
    }

    @SuppressWarnings("unchecked")
    public void extractDerbySeedData(IDataHandler sourceDataHandler, Map<SEED_DATA_OPT, Object> seedOpts, boolean bAppend) {
        try {
            Connection srcConnection = sourceDataHandler.getConnection();

            String destPath = (String) seedOpts.get(SEED_DATA_OPT.DEST_PATH);
            String seedFile = (String) seedOpts.get(SEED_DATA_OPT.SEED_FILE);
            String destSchemaSuffix = (String) seedOpts.get(SEED_DATA_OPT.DEST_SCHEMA_SUFFIX);
            List<String> excludeTables = (List<String>) seedOpts.get(SEED_DATA_OPT.EXCLUDE_TABLES);
            List<String> onlyTables = (List<String>) seedOpts.get(SEED_DATA_OPT.ONLY_TABLES);
            
            if (destPath == null) {
                destPath = DEFAULT_DEST_PATH;
            }
            if (seedFile == null) {
                seedFile = DEFAULT_SEED_FILE;
            }
            //if this is set to create a new file, clear the createdSchemas member too
            if (!bAppend) {
            	createdSchemas.clear();
            }
            
            File outputSeedFile = new File(destPath + "/" + seedFile);
            outputSeedFile.getParentFile().mkdirs();
            FileWriter fw = new FileWriter(outputSeedFile, bAppend);
            BufferedWriter sqlWriter = new BufferedWriter(fw);

            List<String> schemasToCreate = new ArrayList<String>();
            for (String t : sourceDataHandler.getMonitoredTables()) {
            	if (!isWorkableTable(t, onlyTables, excludeTables)) {
            		continue;
            	}
                String s = DatabaseUtils.getSchemaName(t);
                if (!schemasToCreate.contains(s) && !createdSchemas.contains(s)) {
                    schemasToCreate.add(s);
                    createdSchemas.add(s);
                }
            }
            
            //if generating seed data for specific tables, assume the schema already exists
            if (schemasToCreate.size() > 0 && (onlyTables == null || onlyTables.size() == 0 )) {
                exportDerbySchemaInfo(schemasToCreate, sqlWriter, destSchemaSuffix);
            }

            for (String fullyQualifiedTableName : sourceDataHandler.getMonitoredTables()) {

            	if (!isWorkableTable(fullyQualifiedTableName, onlyTables, excludeTables)) {
            		continue;
            	}
                String tableName = DatabaseUtils.getTableName(fullyQualifiedTableName);
                String schemaName = DatabaseUtils.getSchemaName(fullyQualifiedTableName);
                String destFullyQualifiedTableName = schemaName + destSchemaSuffix + "." + tableName;

                String seedDataFileName = sourceDataHandler.getDataHandlerOptions().getDataHandlerPropertyIdentifier()
                        + "_" + destFullyQualifiedTableName + ".dat";

                String seedDataSqlFileName = sourceDataHandler.getDataHandlerOptions().getDataHandlerPropertyIdentifier()
                		+ "_" + destFullyQualifiedTableName + ".sql";

                FileWriter sqlFw = new FileWriter(new File(destPath + "/" + seedDataSqlFileName));
                BufferedWriter sqlBw = new BufferedWriter(sqlFw);

                //create comma delimited data file for this table
                exportDerbyTableData(sqlBw, srcConnection, fullyQualifiedTableName, destSchemaSuffix, destPath + "/" + seedDataFileName);

                //add import command to master sql file
                sqlBw.append("CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('"
                        + schemaName + destSchemaSuffix + "', '"
                        + tableName + "', '"
                        + "${destPath}/" + seedDataFileName
                        + "', NULL, NULL, "
                        + "'utf8', 1)");
                sqlBw.newLine();
                sqlBw.close();
                sqlFw.close();
                sqlWriter.append("@" + seedDataSqlFileName);
                sqlWriter.newLine();
            }

            sqlWriter.close();
            fw.close();

        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (RAFGException e) {
            e.printStackTrace();
        }
    }

    private void exportDerbySchemaInfo(List<String> schemasToCreate, BufferedWriter bw, String destSchemaSuffix) throws IOException {
        for (String schema : schemasToCreate) {
            bw.write("CREATE SCHEMA " + schema + destSchemaSuffix);
            bw.newLine();
        }
        bw.newLine();
    }
    
    /**
     * Returns true if a Derby table can be worked on with extraction
     *   
     * @param monitoredTableName - fully qualified table name
     * @param onlyTables
     * @param excludeTables
     * @return
     */
    private boolean isWorkableTable(String monitoredTableName, List<String> onlyTables, List<String> excludeTables) {
    	String[] splitName = monitoredTableName.split("\\.");
    	monitoredTableName = splitName[splitName.length-1];
    	
    	if (onlyTables != null && onlyTables.size() > 0) {
    		return onlyTables.contains(monitoredTableName);
    	} else if (excludeTables != null && excludeTables.size() > 0) {
    		return !excludeTables.contains(monitoredTableName);
    	}
    	return true;
    }

    /**
     * @param srcConnection SqlConnection object of source database
     * @param fullyQualifiedTableName  table to pull data from
     * @throws SQLException  thrown due to any database errors
     * @throws IOException  thrown due to any output file write errors
     * @throws RAFGException
     */
    private void exportDerbyTableData(BufferedWriter sqlWriter, Connection srcConnection,
            String fullyQualifiedSourceTableName, String destSchemaSuffix, String tableSeedFileName) throws SQLException, IOException, RAFGException {

        String schemaName = DatabaseUtils.getSchemaName(fullyQualifiedSourceTableName);
        String tableName = DatabaseUtils.getTableName(fullyQualifiedSourceTableName);

        String fullyQualifiedTableName = schemaName + destSchemaSuffix + "." + tableName;

        if (bTestMode) {
            RAFGLogger.info("Exporting " + fullyQualifiedTableName + " with TestMode data altering enabled.");
        } else {
            RAFGLogger.info("Exporting " + fullyQualifiedTableName + ".");
        }

        FileWriter fw = new FileWriter(tableSeedFileName);
        BufferedWriter dataSeedWriter = new BufferedWriter(fw);

        //determine primary keys
        DatabaseMetaData dbMeta = srcConnection.getMetaData();
        Set<String> primaryKeys = new HashSet<String>();
        ResultSet dbMetaRs = dbMeta.getPrimaryKeys(null, schemaName, tableName);
        while (dbMetaRs.next()) {
            String columnName = dbMetaRs.getString("COLUMN_NAME");
            primaryKeys.add(columnName);
        }

        //determine if any of the columns are LOBs
        boolean bHasLob = false;
        dbMetaRs = dbMeta.getColumns(null, schemaName, tableName, null);
        while (dbMetaRs.next()) {
            String columnType = dbMetaRs.getString("TYPE_NAME");
            if (columnType.contains("LOB")) {
                bHasLob = true;
            }
        }

        StringBuffer sqlCmd = new StringBuffer();
        if (primaryKeys.size() > 0) {
            sqlCmd.append("SELECT ");
            if (!bHasLob) {
                sqlCmd.append("DISTINCT ");
            }
            sqlCmd.append("* FROM " + fullyQualifiedSourceTableName + " ORDER BY ");
            boolean bFirst = true;
            for (String k : primaryKeys) {
                if (!bFirst) {
                    sqlCmd.append(", ");
                }
                sqlCmd.append(k);
                bFirst = false;
            }

        } else {
            sqlCmd.append("SELECT * FROM " + fullyQualifiedSourceTableName);
        }
        Statement sm = srcConnection.createStatement();
        sm.execute(sqlCmd.toString());
        ResultSet rs = sm.getResultSet();

        sqlWriter.write(generateDropTableString(fullyQualifiedTableName));
        sqlWriter.newLine();
        String createTblCmd = generateCreateTableString(primaryKeys, rs, fullyQualifiedTableName, DEST_DB_TYPE.DERBY);
        sqlWriter.write(createTblCmd);
        sqlWriter.newLine();

        //Create write the header
        ResultSetMetaData meta = rs.getMetaData();
        int cols = meta.getColumnCount();

        boolean bAlterData = false;  //used to determine if data should be altered when test mode is on.

        //write the rows of data
        while (rs.next()) {
            if (bTestMode) {
                bAlterData = testModeAlterDataCalc(5);  //5% chance to not insert this line.
            }
            if (!bAlterData) {
/*
                seedWriter.append("INSERT INTO ");
                seedWriter.append(fullyQualifiedTableName);
                seedWriter.append(" (");
                for (int i = 1; i <= cols; i++) {
                    seedWriter.write(meta.getColumnName(i));
                    if (i != cols) {
                        seedWriter.write(',');
                    }
                }
                seedWriter.append(") VALUES (");
*/
                for (int i = 1; i <= cols; i++) {
                    //use the destination database type since I am writing seed data for it
                    boolean bApplyTestMode = false;
                    if (bTestMode) {
                        bApplyTestMode = !primaryKeys.contains(meta.getColumnName(i)) && testModeAlterDataCalc(5);
                    }
                    String dataStr = translateColumnData(rs.getObject(i), "DERBY", false, meta.getColumnDisplaySize(i), bApplyTestMode);
                    dataSeedWriter.write(dataStr);
                    if (i != cols) {
                        dataSeedWriter.write(',');
                    }
                }
//                seedWriter.append(")");
                dataSeedWriter.newLine();
            }
        }
        rs.close();
        sm.close();

        dataSeedWriter.close();
        fw.close();
    }

    /**
     * @param sourceDataHandler DataHandler of source connection
      */
    public void extractOracleSeedData(IDataHandler sourceDataHandler, Map<SEED_DATA_OPT, Object> seedOpts, boolean bAppend) {
        try {
            Connection srcConnection = sourceDataHandler.getConnection();

            String destPath = (String) seedOpts.get(SEED_DATA_OPT.DEST_PATH);
            String seedFile = (String) seedOpts.get(SEED_DATA_OPT.SEED_FILE);
            String destUsername = (String) seedOpts.get(SEED_DATA_OPT.DEST_USER);
            String destPassword = (String) seedOpts.get(SEED_DATA_OPT.DEST_PASS);
            String destSchemaSuffix = (String) seedOpts.get(SEED_DATA_OPT.DEST_SCHEMA_SUFFIX);
            String destService = (String) seedOpts.get(SEED_DATA_OPT.DEST_SERVICE);

            if (destPath == null) {
                destPath = DEFAULT_DEST_PATH;
            }
            if (seedFile == null) {
                seedFile = DEFAULT_SEED_FILE;
            }
            File outputSeedFile = new File(destPath + "/" + seedFile);
            File outputSqlLdrShFile = new File(destPath + "/load_seed_data.sh");
            File outputSqlLdrBatFile = new File(destPath + "/load_seed_data.bat");

            outputSeedFile.getParentFile().mkdirs();
            FileWriter fw = new FileWriter(outputSeedFile);
            BufferedWriter sqlWriter = new BufferedWriter(fw);

            FileWriter sqlldrShFw = new FileWriter(outputSqlLdrShFile);
            BufferedWriter sqlldrShBw = new BufferedWriter(sqlldrShFw);
            FileWriter sqlldrBatFw = new FileWriter(outputSqlLdrBatFile);
            BufferedWriter sqlldrBatBw = new BufferedWriter(sqlldrBatFw);

            sqlldrShBw.write("#!/bin/sh");
            sqlldrShBw.write(0x0A);

            sqlldrShBw.write("if [ \"$3\" == \"\" ]; then");
            sqlldrShBw.write(0x0A);
            sqlldrShBw.write("  logon=" + destUsername + "/" + destPassword + "@" + destService);
            sqlldrShBw.write(0x0A);
            sqlldrShBw.write("else");
            sqlldrShBw.write(0x0A);
            sqlldrShBw.write("  logon=$1/$2@$3");
            sqlldrShBw.write(0x0A);
            sqlldrShBw.write("fi");
            sqlldrShBw.write(0x0A);

            sqlldrBatBw.write("IF x%3==x");
            sqlldrBatBw.write(" (SET logon=" + destUsername + "/" + destPassword + "@" + destService + ")");
            sqlldrBatBw.write(" ELSE");
            sqlldrBatBw.write(" (SET logon=%1/%2@%3)");
            sqlldrBatBw.write(0x0D);
            sqlldrBatBw.write(0x0A);

            sqlldrShBw.write("sqlplus ${logon} @" + seedFile);
            sqlldrBatBw.write(0x0D);
            sqlldrBatBw.write(0x0A);
            sqlldrBatBw.write("sqlplus %logon% @" + seedFile);
            sqlldrBatBw.write(0x0D);
            sqlldrBatBw.write(0x0A);

//            String[] monitoredSchemas = sourceDataHandler.getMonitoredSchemas();
            List<String> schemasToCreate = new ArrayList<String>();
            for (String t : sourceDataHandler.getMonitoredTables()) {
                String s = DatabaseUtils.getSchemaName(t);
                if (!schemasToCreate.contains(s)) {
                    schemasToCreate.add(s);
                }
            }
            if (schemasToCreate.size() > 0) {
                exportSchemaInfo(schemasToCreate, sqlWriter, destSchemaSuffix);
            }

            currTableNbr = 1;
            nbrTables = sourceDataHandler.getMonitoredTables().length;

            for (String fullyQualifiedTableName : sourceDataHandler.getMonitoredTables()) {
                if (stopWorkerThread) {
                    return;
                }

                String tableName = DatabaseUtils.getTableName(fullyQualifiedTableName);
                String schemaName = DatabaseUtils.getSchemaName(fullyQualifiedTableName);
                String destFullyQualifiedTableName = schemaName + destSchemaSuffix + "." + tableName;
                String seedDataFileName = sourceDataHandler.getDataHandlerOptions().getDataHandlerPropertyIdentifier()
                        + "_" + destFullyQualifiedTableName;

                if (progressBar != null) {
                    int perc = 0;
                    if (nbrTables > 0) {
                        perc = (int) (((float) currTableNbr / (float) nbrTables) * 100);
                    }
                    progressBar.setValue(perc);
                    progressBar.setString(perc + "% (" + currTableNbr + "/" + nbrTables + ")");
                }

                //create comma delimeted data file for this table
                exportTableData(sqlWriter, srcConnection, fullyQualifiedTableName, destPath, destSchemaSuffix, seedDataFileName);
                currTableNbr++;

/*
                //add import command to master sql file
                sqlWriter.append("CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('"
                        + getSchemaName(tableName) + "', '"
                        + getTableName(tableName) + "', '"
                        + "${destPath}/" + seedDataFileName
                        + "', NULL, NULL, "
                        + "'utf8', 1)");
                sqlWriter.newLine();
                sqlWriter.newLine();
*/
                //add sql ldr command to sh and bat files
                sqlldrBatBw.write("sqlldr %logon% control=" + seedDataFileName + ".ctl log=" + seedDataFileName + ".log");
                sqlldrBatBw.write(0x0D);
                sqlldrBatBw.write(0x0A);
                sqlldrShBw.write("sqlldr ${logon} control=" + seedDataFileName + ".ctl log=" + seedDataFileName + ".log");
                sqlldrShBw.write(0x0A);
            }

            sqlWriter.write("EXIT;");
            sqlWriter.newLine();

            sqlWriter.close();
            fw.close();
            sqlldrBatBw.close();
            sqlldrShBw.close();
            sqlldrBatFw.close();
            sqlldrShFw.close();

        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (RAFGException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void exportSchemaInfo(List<String> schemasToCreate, BufferedWriter bw, String destSchemaSuffix) throws IOException {
        for (String schema : schemasToCreate) {
            bw.write("DROP TABLESPACE " + schema + destSchemaSuffix + " INCLUDING CONTENTS;");
            bw.newLine();
            bw.write("CREATE SMALLFILE TABLESPACE " + schema + destSchemaSuffix + " DATAFILE '" + schema + destSchemaSuffix + ".DBF' SIZE 50M REUSE AUTOEXTEND ON;");
            bw.newLine();
            bw.write("DROP USER " + schema + destSchemaSuffix + ";");
            bw.newLine();
            bw.write("CREATE USER " + schema + destSchemaSuffix + " IDENTIFIED BY " + schema + destSchemaSuffix + " DEFAULT TABLESPACE " + schema + destSchemaSuffix + ";");
            bw.newLine();
            bw.write("GRANT ALL PRIVILEGES TO " + schema + destSchemaSuffix + ";");
            bw.newLine();
        }
        bw.newLine();
    }

    /**
     * @param srcConnection SqlConnection object of source database
     * @param fullyQualifiedTableName  table to pull data from
     * @throws SQLException  thrown due to any database errors
     * @throws IOException  thrown due to any output file write errors
     * @throws RAFGException
     */
    private void exportTableData(BufferedWriter sqlWriter, Connection srcConnection,
            String fullyQualifiedTableName, String destPath, String destSchemaSuffix, String tableSeedFileName) throws SQLException, IOException, RAFGException {

        if (bTestMode) {
            RAFGLogger.info("Exporting (" + currTableNbr + "/" + nbrTables + ") " + fullyQualifiedTableName + " with TestMode data altering enabled.");
        } else {
            RAFGLogger.info("Exporting (" + currTableNbr + "/" + nbrTables + ") " + fullyQualifiedTableName + ".");
        }

        String tableSeedDatFileName = tableSeedFileName + ".dat";
        String tableSeedCtlFileName = tableSeedFileName + ".ctl";

        String schemaName = DatabaseUtils.getSchemaName(fullyQualifiedTableName);
        String tableName = DatabaseUtils.getTableName(fullyQualifiedTableName);
        String fullyQualifiedDestTableName = schemaName + destSchemaSuffix + "." + tableName;

        FileWriter fw = new FileWriter(destPath + "/" + tableSeedDatFileName);
        BufferedWriter dataSeedWriter = new BufferedWriter(fw);

        //determine primary keys
        DatabaseMetaData dbMeta = srcConnection.getMetaData();
        Set<String> primaryKeys = new HashSet<String>();
        ResultSet dbMetaRs = dbMeta.getPrimaryKeys(null, schemaName, tableName);
        while (dbMetaRs.next()) {
            String columnName = dbMetaRs.getString("COLUMN_NAME");
            primaryKeys.add(columnName);
        }

        //determine if any of the columns are LOBs
        boolean bHasLob = false;
        dbMetaRs = dbMeta.getColumns(null, schemaName, tableName, null);
        while (dbMetaRs.next()) {
            String columnType = dbMetaRs.getString("TYPE_NAME");
            if (columnType.contains("LOB")) {
                bHasLob = true;
            }
        }

        StringBuffer sqlCmd = new StringBuffer();
        if (primaryKeys.size() > 0) {
            sqlCmd.append("SELECT ");
            if (!bHasLob) {
                sqlCmd.append("DISTINCT ");
            }
            sqlCmd.append("* FROM " + fullyQualifiedTableName + " ORDER BY ");
            boolean bFirst = true;
            for (String k : primaryKeys) {
                if (!bFirst) {
                    sqlCmd.append(", ");
                }
                sqlCmd.append(k);
                bFirst = false;
            }

        } else {
            sqlCmd.append("SELECT * FROM " + fullyQualifiedTableName);
        }
        Statement sm = srcConnection.createStatement();
        sm.execute(sqlCmd.toString());
        ResultSet rs = sm.getResultSet();

        String createTblCmd = generateCreateTableString(primaryKeys, rs, fullyQualifiedDestTableName, DEST_DB_TYPE.ORACLE);
        sqlWriter.write(createTblCmd + ";");
        sqlWriter.newLine();

        //Create write the header
        ResultSetMetaData meta = rs.getMetaData();
        int cols = meta.getColumnCount();

        createCtlFile(destPath, tableSeedCtlFileName, tableSeedDatFileName, meta, fullyQualifiedDestTableName, bHasLob);

        boolean bAlterData = false;  //used to determine if data should be altered when test mode is on.

        //write the rows of data
        while (rs.next()) {
            if (bTestMode) {
                bAlterData = testModeAlterDataCalc(5);  //5% chance to not insert this line.
            }
            if (!bAlterData) {
                for (int i = 1; i <= cols; i++) {
                    //use the destination database type since I am writing seed data for it
                    boolean bApplyTestMode = false;
                    if (bTestMode) {
                        bApplyTestMode = !primaryKeys.contains(meta.getColumnName(i)) && testModeAlterDataCalc(5);
                    }
                    //LOBS need to get written to their own file and referenced
                    if (meta.getColumnTypeName(i).contains("LOB")) {
                        String blobTmpFilename = writeBlobToFile(fullyQualifiedDestTableName, meta.getColumnName(i), rs.getObject(i), destPath);
                        dataSeedWriter.write(blobTmpFilename);
                    } else {
                        String dataStr = translateColumnData(rs.getObject(i), "ORACLE", false, meta.getColumnDisplaySize(i), bApplyTestMode);
                        dataSeedWriter.write(dataStr);
                    }

                    if (i != cols) {
                        dataSeedWriter.write(',');
                    }
                }
                dataSeedWriter.newLine();
            }
        }
        rs.close();
        sm.close();
        dbMetaRs.close();

        dataSeedWriter.close();
        fw.close();
    }

    private String writeBlobToFile(String fullyQualifiedDestTableName, String columnName, Object sqlObject, String destPath) throws IOException, SQLException {
        File lobFile = File.createTempFile(fullyQualifiedDestTableName + "_" + columnName + "_",
                ".lob", new File(destPath));
        OutputStream fos = new FileOutputStream(lobFile);

        if (sqlObject instanceof java.sql.Blob) {
            java.sql.Blob b = (java.sql.Blob) sqlObject;
            long blobLen = b.length();
            fos.write(b.getBytes(1, (int) blobLen));
            fos.close();
        }
        return lobFile.getName();
    }

    private void createCtlFile(String destPath, String tableSeedCtlFileName, String tableSeedDatFileName, 
            ResultSetMetaData meta, String fullyQualifiedTableName,
            boolean bHasLob) throws IOException, SQLException {

        FileWriter fw = new FileWriter(destPath + "/" + tableSeedCtlFileName);
        BufferedWriter ctlWriter = new BufferedWriter(fw);
/*
        LOAD DATA
        INFILE
        APPEND
        INTO TABLE VRU_Node
        FIELDS TERMINATED BY ","
        OPTIONALLY ENCLOSED BY '"'
        TRAILING NULLCOLS
        (
           VRU_Node_Id,
           VRU_Node_Nm,
           VRU_Node_Lst_Rdy_Dt  DATE 2003-01-02 13:21:23.991
           VRU_Cty_Cd,
           VRU_Node_Enbl
        )
*/

        ctlWriter.write("LOAD DATA");
        ctlWriter.newLine();
        ctlWriter.write("INFILE '" + tableSeedDatFileName + "'");
        ctlWriter.newLine();
        ctlWriter.write("APPEND");
        ctlWriter.newLine();
        ctlWriter.write("INTO TABLE " + fullyQualifiedTableName);
        ctlWriter.newLine();
        ctlWriter.write("FIELDS TERMINATED BY \",\"");
        ctlWriter.newLine();
        ctlWriter.write("OPTIONALLY ENCLOSED BY '\"'");
        ctlWriter.newLine();
        ctlWriter.write("TRAILING NULLCOLS");
        ctlWriter.newLine();
        ctlWriter.write("(");
        ctlWriter.newLine();
        int cols = meta.getColumnCount();
        for (int i = 1; i <= cols; i++) {
            ctlWriter.write(meta.getColumnName(i));
            String colTypeName = meta.getColumnTypeName(i);
            if (colTypeName.equalsIgnoreCase("TIMESTAMP")) {
                ctlWriter.write(" TIMESTAMP \"YYYY-MM-DD HH24:MI:SS.FF3\"");
            } else if (colTypeName.equalsIgnoreCase("DATE")) {
                ctlWriter.write(" DATE \"YYYY-MM-DD\"");
            } else if (colTypeName.equalsIgnoreCase("BLOB")) {
                ctlWriter.write(" LOBFILE(" + meta.getColumnName(i) + "_filename) TERMINATED BY EOF,");
                ctlWriter.newLine();
                ctlWriter.write(" " + meta.getColumnName(i) + "_filename    FILLER CHAR(100)");
            } else if (colTypeName.equalsIgnoreCase("CHAR") || colTypeName.equalsIgnoreCase("CHAR")) {
                ctlWriter.write(" " + colTypeName + "(" + meta.getColumnDisplaySize(i) + ")");
            }
            if (i != cols) {
                ctlWriter.write(',');
            }
            ctlWriter.newLine();
        }
        ctlWriter.write(")");
        ctlWriter.newLine();
        ctlWriter.close();
        fw.close();
    }

    /**
     * calculate if testmode should be enabled for altering a bit of data based on a percentage chance.
     * @param perc Percentage chance to alter data.  I.e. 5 for 5%
     * @return true to alter the data.
     */
    private boolean testModeAlterDataCalc(int perc) {
        return ((random.nextFloat()*100) <= perc);
    }

    private String generateDropTableString(String fullyQualifiedTableName) {
        //String tableName = getTableName(fullyQualifiedTableName);
        StringBuffer sqlCmdSb = new StringBuffer();
        sqlCmdSb.append("DROP TABLE ");
        sqlCmdSb.append(fullyQualifiedTableName);
        return sqlCmdSb.toString();
    }
    /**
     * @param srcConnection
     * @param tableName
     * @return
     * @throws SQLException
     */
    private String generateCreateTableString(Set<String> primaryKeys, ResultSet resultSet, String fullyQualifiedTableName, DEST_DB_TYPE destDbType) throws SQLException {
/*
        CREATE TABLE ivr_officehours (Description varchar(50) PRIMARY KEY NOT NULL, Extension varchar(20), CloseDate timestamp, CloseTime timestamp, OpenDate timestamp, OpenTime timestamp, CloseDOW varchar(10), mod char(8) for bit data)
        INSERT INTO ivr_officehours (Description,Extension,CloseDate,CloseTime,OpenDate,OpenTime,CloseDOW) VALUES ('Test 1',null,{ts '2008-07-01 00:00:00.000'},null,{ts '2008-07-02 00:00:00.000'},null,null)
*/

//        String tableName = getTableName(fullyQualifiedTableName);

        int nbrNewlines = 0;

        StringBuffer sqlCmdSb = new StringBuffer();
        sqlCmdSb.append("CREATE TABLE ");
        sqlCmdSb.append(fullyQualifiedTableName);
        sqlCmdSb.append(" (");
/*
        if (fullyQualifiedTableName.equalsIgnoreCase("CCMS_CACHE.BKG_ADVY_MSG")) {
        	sqlCmdSb.getClass();
        }
*/        
        ResultSetMetaData meta = resultSet.getMetaData();
        int cols = meta.getColumnCount();
        for (int i = 1; i <= cols; i++) {
            String name = meta.getColumnName(i);
            sqlCmdSb.append(name + " ");
            String type = meta.getColumnTypeName(i);
            int size = meta.getColumnDisplaySize(i);
            int scale = meta.getScale(i);
            int nullable = meta.isNullable(i);

            if (destDbType == DEST_DB_TYPE.DERBY) {
//            if (type.equalsIgnoreCase("DATE")) {
//                sqlCmdSb.append("DATE");
//            } else {
                if (type.equalsIgnoreCase("VARCHAR2")) {
                    type = "VARCHAR";
                } else if (type.equalsIgnoreCase("NUMBER")) {
                    type = "NUMERIC";
                } else if (type.equalsIgnoreCase("LONG")) {
                    type = "CLOB";
                }

            }

                sqlCmdSb.append(type);
                if (size > 0 && !type.contains("LOB") && !type.contains("DATE")) {
                    if (scale <= 0) {
                        sqlCmdSb.append("(" + size + ")");
                    } else {
                        sqlCmdSb.append("(" + size + ", " + scale + ")");
                    }

                }
//            }

            if (nullable == ResultSetMetaData.columnNoNulls) {
                sqlCmdSb.append(" NOT NULL");
            }

            if (i < cols) {
                sqlCmdSb.append(",");
            }

            //check if I need to add a newline - only if the line has grown more than 512 bytes.  There is a line limit...
            //since ";" terminates the command, you can split lines up...
            if (destDbType == DEST_DB_TYPE.ORACLE) {
	            long currNbrNewLinesNeeded = sqlCmdSb.length() / 512;
	            if (currNbrNewLinesNeeded > nbrNewlines) {
	                nbrNewlines++;
	                sqlCmdSb.append("\n");
	            }
            }
        }

        boolean bFirst = true;
        if (primaryKeys.size() > 0) {
            sqlCmdSb.append(", PRIMARY KEY (");
            for (String key : primaryKeys) {
                if (!bFirst) {
                    sqlCmdSb.append(", ");
                } else {
                    bFirst = false;
                }
                sqlCmdSb.append(key);
            }
            sqlCmdSb.append(")");
        }

        sqlCmdSb.append(")");

        return sqlCmdSb.toString();
    }


    /**
     * @param fullyQualifiedTableName
     * @return
     */
/*
    private String getSchemaName(String fullyQualifiedTableName) {
        String[] tokens = fullyQualifiedTableName.split("[.](?=([^\"]*\"[^\"]*\")*[^\"]*$)");
        String retStr = null;
        if (tokens != null && tokens.length > 1) {
            retStr = tokens[0].trim().replace("\"", "");
        }
        return retStr;
    }
*/
    /**
     * @param fullyQualifiedTableName
     * @return
     */
/*
    private String getTableName(String fullyQualifiedTableName) {
        String[] tokens = fullyQualifiedTableName.split("[.](?=([^\"]*\"[^\"]*\")*[^\"]*$)");
        String retStr = null;
        if (tokens != null && tokens.length > 0) {
            retStr = tokens[tokens.length - 1].trim().replace("\"", "");
        }
        return retStr;
    }
*/
    /**
     * Translate column data for placement in bulk import file for Apache Derby
     * bulk insert.
     * @param inputObj
     * @param sqlServerProductName
     * @return
     * @throws SQLException
     * @throws RAFGException
     */
    private String translateColumnData(Object inputObj, String sqlServerProductName, boolean includeEqualSign, int columnMaxLength, boolean bApplyTestMode) throws SQLException, RAFGException
    {
        if (inputObj == null && includeEqualSign) {
            return " IS NULL";
        }

        if (inputObj == null) {
            return "";
        }
        final StringBuilder retStr = new StringBuilder();
        boolean quotes = true;
        if (includeEqualSign) {
            retStr.append("=");
        }
        if (inputObj instanceof byte[] || inputObj instanceof java.sql.Blob) {
            if (sqlServerProductName.toUpperCase().contains("DERBY")) {
                quotes = false;
            } else if (sqlServerProductName.toUpperCase().contains("MICROSOFT SQL SERVER")) {
                retStr.append("0x");
                quotes = false;
            } else if (sqlServerProductName.toUpperCase().contains("MYSQL")) {
                retStr.append("x");
            } else if (sqlServerProductName.toUpperCase().contains("ORACLE")) {
                retStr.append("X");
            }

            if (quotes) {
                retStr.append("'");
            }
            if (inputObj instanceof byte[]) {
                retStr.append(DatabaseUtils.getHex((byte[]) inputObj));
            } else if (inputObj instanceof java.sql.Blob) {
                java.sql.Blob blob = (java.sql.Blob) inputObj;
                if (blob.length() > Integer.MAX_VALUE) {
                    throw new RAFGException("BLOB data cannot contain more than " + Integer.MAX_VALUE + " bytes of data when generating seed file(s).");
                }
                retStr.append(DatabaseUtils.getHex(blob.getBytes((long) 1, (int) blob.length())));
            } else {
                retStr.append(inputObj);
            }
            if (quotes) {
                retStr.append("'");
            }
//            if (bDerby) {
  //              retStr.append(" AS BLOB)");
//            }
        } else if (inputObj instanceof java.sql.Timestamp || inputObj instanceof java.util.Date) {
            retStr.append("\"");
            retStr.append(inputObj);
            retStr.append("\"");
        } else if (inputObj instanceof String) {
            //need to replace single ' with double '' so that they can be inserted properly.
            String str = ((String) inputObj).replace("\"", "\"\"");
//            String str = (String) inputObj;
            retStr.append("\"");

            // only string data is altered in testmode.
            if (bApplyTestMode) {
                String testStr = Long.toString(Math.abs(random.nextLong()), 36);
                if (testStr.length() > columnMaxLength) {
                    testStr = testStr.substring(0, columnMaxLength);
                }
                retStr.append(testStr);
            } else {
                retStr.append(str);
            }

            retStr.append("\"");
        } else {
            retStr.append(inputObj);
        }
        return retStr.toString();
    }

    /**
     * Converts a raw byte array to a hexadecimal representation String.
     * @param raw
     * @return String
     */
/*    
    public static String getHex(final byte [] raw) {
        if (raw == null) {
          return null;
        }
        final StringBuilder hex = new StringBuilder( 2 * raw.length );
        for (final byte b : raw) {
          hex.append(HEXES.charAt((b & 0xF0) >> 4))
             .append(HEXES.charAt((b & 0x0F)));
        }
        try {
            java.io.FileOutputStream f = new java.io.FileOutputStream("C:\\temp\\cfg.zip");
            f.write(raw);
            f.close();
        }
        catch ( IOException e ) {
            //e.printStackTrace();
        }
        return hex.toString();
    }
*/
    /**
     * @param resultSet - The ResultSet to convert to a String
     * @return Row contents in debug format
     * @throws SQLException - Thrown if any database exception occurs determining column contents.
     */
/*    
    private String dumpRow(ResultSet resultSet) throws SQLException {
        final StringBuilder sb = new StringBuilder();

        ResultSetMetaData meta = resultSet.getMetaData();
        int cols = meta.getColumnCount();
        for (int i = 1; i <= cols; i++) {
            sb.append(meta.getColumnName(i));
            sb.append("[");
            sb.append(resultSet.getObject(i));
            sb.append("]");
            if (i != cols) {
                sb.append(",");
            }
        }
        return sb.toString();
    }
*/
    private static String getInstructions() {
        StringBuilder sb = new StringBuilder();

        sb.append("-h --help   This help info\n");
        sb.append("-s          Source URL");
        sb.append("-u          User");
        sb.append("-p          Password");
        sb.append("-d          Destination path for SQL*Loader data/scripts");
        sb.append("-os         Destination Oracle XE Cache Schema Suffix (Default: _CACHE)");
        sb.append("-ou         Destination Oracle XE Cache User");
        sb.append("-op         Destination Oracle XE Cache Password");
        sb.append("-gds        Perform a GenericDataSource extraction on the given class.");
        sb.append("            (Note this disables the normal source database extraction.)");
        sb.append("            (This parameter can be specified more than once)");
        sb.append("-excludeTables 	List of tables to exlude with GenericDataSource extraction");
        sb.append("-onlyTables		List of tables to use with GenericDataSource extraction");
//        sb.append("-t          Temp SQL output path");

        return sb.toString();
    }

    /**
     * @param args - command line arguments
     * @throws Exception
     */
    public static void main(final String[] args) throws Exception {
        if (args == null || args.length == 0) {
            GenerateSeedData instance = new GenerateSeedData();
            instance.loadGui();
        } else {
            try {
                procCmdLine(args);
            }
            catch ( RAFGException e ) {
                throw new Exception(e);
            }
            catch ( IOException e ) {
                throw new Exception(e);
            }
        }
    }

    /**
     * Process Command Line arguments for running from command line.
     * @param args arguments passed
     * @throws RAFGException
     * @throws IOException
     */
    private static void procCmdLine(final String[] args) throws RAFGException, IOException {
        int numArgs = args.length;
        String seedFileName = null;
        String destPath = null;
        String destService = null;
        String user = null;
        String pass = null;
        String destUser = null;
        String destPass = null;
        String destSchemaSuffix = "_SCHEMA";
        String sourceUrl = null;
        String destDbType = DEST_DB_TYPE.DERBY.toString();
        boolean bAppend = false;
        List<String> genericDataSourceClasses = null;
        
        /*
         * Exclude and Only tables are added to support using of specific environment requests 
         * in separate executions and work with gds classes
         * (Started with Amenities SOA ref data which requires SSL configuration when pointing 
         * to Production environment.) 
         */
        List<String> excludeTables = null;
        List<String> onlyTables = null;
        
        for (int i = 0; i < numArgs; i++) {
            String opt = args[i];
            if (opt.equalsIgnoreCase("-h")
                    || opt.equalsIgnoreCase("-help")
                    || opt.equalsIgnoreCase("--help")
                    || opt.equalsIgnoreCase("-?")) {
                RAFGLogger.info(getInstructions());
            } else if (opt.equalsIgnoreCase("-s")) {
                i++;
                if (i < numArgs) {
                    sourceUrl = args[i];
                }
            } else if (opt.equalsIgnoreCase("-u")) {
                i++;
                if (i < numArgs) {
                    user = args[i];
                }
            } else if (opt.equalsIgnoreCase("-p")) {
                i++;
                if (i < numArgs) {
                    pass = args[i];
                }
            } else if (opt.equalsIgnoreCase("-d")) {
                i++;
                if (i < numArgs) {
                    destPath = args[i];
                }
            } else if (opt.equalsIgnoreCase("-os")) {
                i++;
                if (i < numArgs) {
                    destSchemaSuffix = args[i];
                }
            } else if (opt.equalsIgnoreCase("-ou")) {
                i++;
                if (i < numArgs) {
                    destUser = args[i];
                }
            } else if (opt.equalsIgnoreCase("-op")) {
                i++;
                if (i < numArgs) {
                    destPass = args[i];
                }
            } else if (opt.equalsIgnoreCase("-ot")) {
                i++;
                if (i < numArgs) {
                    destService = args[i];
                }
            } else if (opt.equalsIgnoreCase("-odt")) {
            	i++;
            	if (i < numArgs) {
            		destDbType = args[i];
            	}

/*
            } else if (opt.equalsIgnoreCase("-t")) {
                i++;
                if (i < numArgs) {
                    sqlPath = args[i];
                }
*/
            //            } else if (opt.equalsIgnoreCase("-f")) {
//                i++;
//                if (i < numArgs) {
//                    seedFileName = args[i];
//                }
            } else if (opt.equalsIgnoreCase("-gds")) {
            	i++;
            	if (i < numArgs) {
            		if (genericDataSourceClasses == null) {
            			genericDataSourceClasses = new ArrayList<String>();
            		}
            		genericDataSourceClasses.add(args[i]);
            	}
            } else if (opt.equalsIgnoreCase("-excludeTables")) {
            	i++;
            	if (i < numArgs) {
            		if (excludeTables == null) {
            			excludeTables = new ArrayList<String>();
            		}
            		excludeTables.add(args[i]);
            	}
            } else if (opt.equalsIgnoreCase("-onlyTables")) {
            	i++;
            	if (i < numArgs) {
            		if (onlyTables == null) {
            			onlyTables = new ArrayList<String>();
            		}
            		onlyTables.add(args[i]);
            	}
            } else if (opt.equalsIgnoreCase("-append")) {
            	i++;
            	bAppend = true;
            }
            
        }
        //check params
        if (destPath == null) {
            RAFGLogger.info("Need destination path.");
            return;
        }

        if (seedFileName == null) {
            RAFGLogger.info("Using default filename:   " + DEFAULT_SEED_FILE);
            seedFileName = DEFAULT_SEED_FILE;
        } /* else {
            System.out.println("Using filename:           " + seedFileName);
        } */
/*        
        if (user == null) {
            System.out.println("Using default username:   " + DEFAULT_USERNAME);
            user = DEFAULT_USERNAME;
        } else {
            System.out.println("Using username:           " + user);
        }
        if (pass == null) {
            System.out.println("Using default password:   xxx"); //" + DEFAULT_PASSWORD);
            pass = DEFAULT_PASSWORD;
        } else {
            System.out.println("Using password:           xxx");  //" + pass);
        }
        if (sourceUrl == null) {
            System.out.println("Using default source URL: " + DEFAULT_SOURCE_URL);
            sourceUrl = DEFAULT_SOURCE_URL;
        } else {
            System.out.println("Using source URL:         " + sourceUrl);
        }
*/
        if (DEST_DB_TYPE.valueOf(destDbType) == DEST_DB_TYPE.ORACLE) {
	        if (user == null) {
	            RAFGLogger.info("Using default Oracle XE username:   " + DEFAULT_DEST_USERNAME);
	            user = DEFAULT_DEST_USERNAME;
	        } else {
	            RAFGLogger.info("Using Oracle XE Cache username:     " + destUser);
	        }
	        if (pass == null) {
	            RAFGLogger.info("Using default Oracle XE password:   xxx"); // + DEFAULT_DEST_PASSWORD);
	            pass = DEFAULT_DEST_PASSWORD;
	        } else {
	            RAFGLogger.info("Using Oracle XE Cache password:     xxx"); // + destPass);
	        }

	        if (destService == null) {
	            RAFGLogger.info("Using default Oracle XE service:    " + DEFAULT_DEST_SERVICE);
	            destService = DEFAULT_DEST_SERVICE;
	        } else {
	            RAFGLogger.info("Using Oracle XE Cache service:      " + destService);
	        }
        }

        RAFGLogger.info("Using Oracle Cache Schema suffix:    " + destSchemaSuffix);

        GenerateSeedData instance = new GenerateSeedData();
        Map<SEED_DATA_OPT, Object> seedGenParams = new HashMap<SEED_DATA_OPT, Object>();
        seedGenParams.put(SEED_DATA_OPT.SOURCE_URL, sourceUrl);
        seedGenParams.put(SEED_DATA_OPT.SOURCE_USER, user);
        seedGenParams.put(SEED_DATA_OPT.SOURCE_PASS, pass);
        seedGenParams.put(SEED_DATA_OPT.DEST_PATH, destPath);
        seedGenParams.put(SEED_DATA_OPT.SEED_FILE, seedFileName);
        seedGenParams.put(SEED_DATA_OPT.DEST_USER, destUser);
        seedGenParams.put(SEED_DATA_OPT.DEST_PASS, destPass);
        seedGenParams.put(SEED_DATA_OPT.DEST_SERVICE, destService);
        seedGenParams.put(SEED_DATA_OPT.DEST_SCHEMA_SUFFIX, destSchemaSuffix);
        seedGenParams.put(SEED_DATA_OPT.DEST_DB_TYPE, destDbType);
        seedGenParams.put(SEED_DATA_OPT.GENERICDATASOURCECLASSES, genericDataSourceClasses);
        seedGenParams.put(SEED_DATA_OPT.EXCLUDE_TABLES, excludeTables);
        seedGenParams.put(SEED_DATA_OPT.ONLY_TABLES, onlyTables);
        seedGenParams.put(SEED_DATA_OPT.APPEND_TO_DEST, bAppend);
        instance.extractSeedData(seedGenParams);

/*
        IDataHandler cacheDataHandler = DataHandlerFactory.getCacheDataHandler();
        DataHandlerOptions cacheOpts = new DataHandlerOptions();
        cacheOpts.addOverideOption(DataHandlerOptions.OVERIDE_OPTION.IGNORE_JNDI);
        Map<String, Object> cacheOptsMap = cacheOpts.getDataHandlerSpecificOptionsMap();
        File cachePath = new File(destPath);
        cacheOptsMap.put("DataBasePath", cachePath.getCanonicalPath());
        cacheDataHandler.initialize(cacheOpts);

        IDataHandler seedHandler = DataHandlerFactory.getSeedDataHandler();
        DataHandlerOptions seedOpts = new DataHandlerOptions();
        seedOpts.setConfigSettings(ConfigSettings.getSingleton());
        Map<String, Object> seedOptsMap = seedOpts.getDataHandlerSpecificOptionsMap();
        seedOptsMap.put("CacheSqlConnection", cacheDataHandler.getConnection());
        seedOptsMap.put("SeedFile", new File(destPath, seedFileName).getCanonicalPath());
        seedHandler.initialize(seedOpts);
*/
    }

    /**
     * Turn on/off test data generation mode.  When data is extracted from
     * the reference data tables, it will be altered some with random data and
     * missing rows.  This allows for testing the syncronizing logic.
     * @param testMode  true to enable test data generation.
     */
    public void setTestMode(boolean testMode) {
        bTestMode = testMode;
    }

    private class WorkerRunnable implements Runnable {

        private Map<SEED_DATA_OPT, Object> seedOpts;

        @Override
        public void run() {
            extractSeedData(seedOpts);
        }

        public void setSeedOpts(Map<SEED_DATA_OPT, Object> seedOpts) {
            this.seedOpts = seedOpts;
        }

    }

}
